{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# SQLAlchemy ORM 基础\r\n",
    "\r\n",
    "## Inserting Data 插入数据\r\n",
    "\r\n",
    "要使用 SQLAlchemy 创建一个新记录，我们按照以下步骤操作:\r\n",
    "\r\n",
    "1. Create an object. 创建一个对象\r\n",
    "2. Add the object to the session. 将对象添加到会话中\r\n",
    "3. Commit the session. 提交会话\r\n",
    "\r\n",
    "在 SQLAlchemy 中，我们使用会话 `session` 与数据库交互。幸运的是，我们不需要手动创建会话，Flask-SQLAlchemy 为我们管理这些。我们以 `db.session` 的形式访问会话对象。它是处理到数据库连接的会话对象。会话对象也是事务的处理程序。默认情况下，`事务隐式启动并保持打开状态`，直到会话被提交或回滚。\r\n",
    "\r\n",
    "~~启动 Python shell 并创建一些模型对象，如下所示:~~\r\n",
    "## 补充[不同处]: 我们尝试采用 jupyter notebook 来完成SHELL的功能, 并对多处代码做了合并, 数据库查询直接用mysql客户端的的ASCII 而非图片."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "source": [
    "#  注: jupyter notebook 运行这段后会显示每个中间变量的输出, 不然默认只显示最后一个变量的输出或者不显示.\r\n",
    "from IPython.core.interactiveshell import InteractiveShell\r\n",
    "InteractiveShell.ast_node_interactivity = \"all\"  "
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "补充[不同处] 创建一个上下文对象,来模拟运行. （不需要，因为是 db = SQLAlchemy(app) 将实例绑定到一个非常特定的 Flask 应用程序）\r\n",
    "https://flask-sqlalchemy.palletsprojects.com/en/2.x/api/#configuration\r\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "source": [
    "from main2 import app\r\n",
    "from flask import url_for\r\n",
    "\r\n",
    "app.config[\"SQLALCHEMY_ECHO\"] = True\r\n",
    "app.config['DATABASE_QUERY_TIMEOUT'] = 1\r\n",
    "app.config['SQLALCHEMY_RECORD_QUERIES'] = True\r\n",
    "\r\n",
    "# SQLALCHEMY_RECORD_QUERIES\r\n",
    "# with app.test_request_context('/api'): # /api is arbitrarily chosen. /API是任意选择的,无关紧要\r\n",
    "#     url_for('index')\r\n",
    "\r\n",
    "# app_context = app.app_context()\r\n",
    "# app_context.push() \r\n",
    "# 不需要  The difference between the two is that in the first case methods like create_all() and drop_all() will work all the time \r\n",
    "# but in the second case a flask.Flask.app_context() has to exist.\r\n",
    "\r\n",
    "\r\n",
    "\r\n",
    "app.config"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<Config {'ENV': 'production', 'DEBUG': True, 'TESTING': False, 'PROPAGATE_EXCEPTIONS': None, 'PRESERVE_CONTEXT_ON_EXCEPTION': None, 'SECRET_KEY': 'a really really really really long secret key', 'PERMANENT_SESSION_LIFETIME': datetime.timedelta(days=31), 'USE_X_SENDFILE': False, 'SERVER_NAME': None, 'APPLICATION_ROOT': '/', 'SESSION_COOKIE_NAME': 'session', 'SESSION_COOKIE_DOMAIN': None, 'SESSION_COOKIE_PATH': None, 'SESSION_COOKIE_HTTPONLY': True, 'SESSION_COOKIE_SECURE': False, 'SESSION_COOKIE_SAMESITE': None, 'SESSION_REFRESH_EACH_REQUEST': True, 'MAX_CONTENT_LENGTH': None, 'SEND_FILE_MAX_AGE_DEFAULT': datetime.timedelta(seconds=43200), 'TRAP_BAD_REQUEST_ERRORS': None, 'TRAP_HTTP_EXCEPTIONS': False, 'EXPLAIN_TEMPLATE_LOADING': False, 'PREFERRED_URL_SCHEME': 'http', 'JSON_AS_ASCII': True, 'JSON_SORT_KEYS': True, 'JSONIFY_PRETTYPRINT_REGULAR': False, 'JSONIFY_MIMETYPE': 'application/json', 'TEMPLATES_AUTO_RELOAD': None, 'MAX_COOKIE_SIZE': 4093, 'SQLALCHEMY_DATABASE_URI': 'mysql+pymysql://root:flask123@localhost/flask_app_db', 'SQLALCHEMY_TRACK_MODIFICATIONS': True, 'SQLALCHEMY_BINDS': None, 'SQLALCHEMY_NATIVE_UNICODE': None, 'SQLALCHEMY_ECHO': False, 'SQLALCHEMY_RECORD_QUERIES': None, 'SQLALCHEMY_POOL_SIZE': None, 'SQLALCHEMY_POOL_TIMEOUT': None, 'SQLALCHEMY_POOL_RECYCLE': None, 'SQLALCHEMY_MAX_OVERFLOW': None, 'SQLALCHEMY_COMMIT_ON_TEARDOWN': False, 'SQLALCHEMY_ENGINE_OPTIONS': {}}>"
      ]
     },
     "metadata": {},
     "execution_count": 2
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "source": [
    "# 这里我们创建了两个 Category 对象:\r\n",
    "\r\n",
    "from main2 import db, Post, Tag, Category\r\n",
    "\r\n",
    "c1 = Category(name='Python', slug='python')\r\n",
    "c2 = Category(name='Java', slug='java')\r\n",
    "\r\n",
    "c1, c2\r\n",
    "\r\n",
    "# 接下来，我们将对象添加到会话中。\r\n",
    "\r\n",
    "db.session.add(c1)\r\n",
    "db.session.add(c2)\r\n",
    "\r\n",
    "c1.id, c2.id"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(<None:Python>, <None:Java>)"
      ]
     },
     "metadata": {},
     "execution_count": 3
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(None, None)"
      ]
     },
     "metadata": {},
     "execution_count": 3
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "向会话中添加对象实际上并不会将它们写入数据库，它只准备在下一次提交时保存的对象。我们可以通过检查对象的主键来验证这一点。\r\n",
    "这两个对象的 id 属性值都是 None。这意味着我们的对象还没有保存在数据库中。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "source": [
    "# 我们可以使用 `add_all()` 方法，而不是一次向会话中添加一个对象。`add_all()` 方法接受要添加到会话的对象列表(list)。\r\n",
    "\r\n",
    "db.session.add_all([c1, c1])\r\n",
    "\r\n",
    "# 多次向会话中添加对象不会引发任何错误。在任何时候，您都可以使用 db.session.new 查看会话中的对象。\r\n",
    "\r\n",
    "db.session.new\r\n",
    "\r\n",
    "# 最后，将对象保存到数据库调用 commit ()方法，如下所示:\r\n",
    "\r\n",
    "db.session.commit() \r\n",
    "\r\n",
    "# 访问 Category 对象的 id 属性现在将返回主键，而不是 None。\r\n",
    "\r\n",
    "c1.id, c2.id"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "IdentitySet([<None:Python>, <None:Java>])"
      ]
     },
     "metadata": {},
     "execution_count": 4
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(1, 2)"
      ]
     },
     "metadata": {},
     "execution_count": 4
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "此时，mysql 中的 categories 表应该是这样的:\r\n",
    "```\r\n",
    "mysql> select * from categories;\r\n",
    "+----+--------+--------+---------------------+\r\n",
    "| id | name   | slug   | created_on          |\r\n",
    "+----+--------+--------+---------------------+\r\n",
    "|  1 | Python | python | 2021-08-27 07:21:51 |\r\n",
    "|  2 | Java   | java   | 2021-08-27 07:21:51 |\r\n",
    "+----+--------+--------+---------------------+\r\n",
    "2 rows in set (0.00 sec)\r\n",
    "\r\n",
    "```\r\n",
    "我们新创建的类别与任何文章都没有关联，因此 c1.posts 和 c2.posts 将返回一个空列表。\r\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "source": [
    "c1.posts, c2.posts"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "([], [])"
      ]
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 现在让我们创建一些帖子。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "source": [
    "p1 = Post(title='Post 1', slug='post-1', content='Post 1', category=c1)\r\n",
    "p2 = Post(title='Post 2', slug='post-2', content='Post 2', category=c1)\r\n",
    "p3 = Post(title='Post 3', slug='post-3', content='Post 3', category=c2)\r\n",
    "\r\n",
    "# 在创建 Post 对象时，我们不需要传递类别，我们也可以设置如下:\r\n",
    "c1,p1\r\n",
    "p1.category = c1\r\n",
    "p1, p1.category"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(<1:Python>, <None:Post 1>)"
      ]
     },
     "metadata": {},
     "execution_count": 6
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(<None:Post 1>, <1:Python>)"
      ]
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "source": [
    "# 将对象添加到会话并提交。\r\n",
    "db.session.add_all([p1,p2,p3])\r\n",
    "\r\n",
    "# db.session.commit  注意必须 带括号\r\n",
    "db.session.commit()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "```sql\r\n",
    "mysql> select * from posts;\r\n",
    "+----+--------+--------+---------+---------------------+---------------------+-------------+\r\n",
    "| id | title  | slug   | content | created_on          | updated_on          | category_id |\r\n",
    "+----+--------+--------+---------+---------------------+---------------------+-------------+\r\n",
    "|  1 | Post 1 | post-1 | Post 1  | 2021-08-27 07:35:53 | 2021-08-27 07:35:53 |           1 |\r\n",
    "|  2 | Post 2 | post-2 | Post 2  | 2021-08-27 07:35:53 | 2021-08-27 07:35:53 |           1 |\r\n",
    "|  3 | Post 3 | post-3 | Post 3  | 2021-08-27 07:35:53 | 2021-08-27 07:35:53 |           2 |\r\n",
    "+----+--------+--------+---------+---------------------+---------------------+-------------+\r\n",
    "3 rows in set (0.00 sec)\r\n",
    "\r\n",
    "```"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "source": [
    "# 再次访问 Category 对象的 posts 属性，这次你会得到一个非空的列表，如下所示:\r\n",
    "c1.posts , c2.posts\r\n",
    "\r\n",
    "# 从关系的另一边，我们可以使用 Post 对象上的 Category 属性访问文章所属的 Category 对象。\r\n",
    "p1.category , p2.category, p3.category"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "([<1:Post 1>, <2:Post 2>], [<3:Post 3>])"
      ]
     },
     "metadata": {},
     "execution_count": 8
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(<1:Python>, <1:Python>, <2:Java>)"
      ]
     },
     "metadata": {},
     "execution_count": 8
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "请记住，由于 Category 模型中的 `relationship()` 指令，所有这些都成为可能。现在我们的数据库中有三个帖子，但是没有一个与任何标签相关联。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "source": [
    "p1.tags, p2.tags, p3.tags"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "([], [], [])"
      ]
     },
     "metadata": {},
     "execution_count": 9
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 是时候创建一些标签了。在 shell 中创建 Tag 对象如下:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "source": [
    "t1 = Tag(name=\"refactoring\", slug=\"refactoring\")\r\n",
    "t2 = Tag(name=\"snippet\", slug=\"snippet\")\r\n",
    "t3 = Tag(name=\"analytics\", slug=\"analytics\")\r\n",
    "\r\n",
    "db.session.add_all([t1, t2, t3])\r\n",
    "db.session.commit()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "```sql\r\n",
    "mysql> select * from tags;\r\n",
    "+----+-------------+-------------+---------------------+\r\n",
    "| id | name        | slug        | created_on          |\r\n",
    "+----+-------------+-------------+---------------------+\r\n",
    "|  1 | refactoring | refactoring | 2021-08-27 07:47:38 |\r\n",
    "|  2 | snippet     | snippet     | 2021-08-27 07:47:38 |\r\n",
    "|  3 | analytics   | analytics   | 2021-08-27 07:47:38 |\r\n",
    "+----+-------------+-------------+---------------------+\r\n",
    "3 rows in set (0.00 sec)\r\n",
    "\r\n",
    "```"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "这段代码创建三个标记对象并将它们提交到数据库。我们的帖子仍然没有连接到任何标签。在这里，我们看看 如何连接一个 `Post` 对象到一个 `Tag` 对象。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "source": [
    "app.config[\"SQLALCHEMY_ECHO\"] = True"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "source": [
    "p1.tags.append(t1) \r\n",
    "p1.tags.extend([t2, t3])\r\n",
    "p2.tags.append(t2)\r\n",
    "# p3.tags.append([t2, t3])  #'list' object has no attribute '_sa_instance_state'\r\n",
    "p3.tags.extend([t2, t3])\r\n",
    "\r\n",
    "db.session.add_all([p1, p2, p3]) \r\n",
    "db.session.new\r\n"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "IdentitySet([])"
      ]
     },
     "metadata": {},
     "execution_count": 24
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "source": [
    "# 会话本身就像一个集合，所有的项目都可以通过迭代器接口访问:\r\n",
    "for obj in db.session:\r\n",
    "    print(obj)\r\n"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "<2:Java>\n",
      "<1:Python>\n",
      "<3:Post 3>\n",
      "<1:Post 1>\n",
      "<2:Post 2>\n",
      "<2:snippet>\n",
      "<1:refactoring>\n",
      "<3:analytics>\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "source": [
    "# pending objects recently added to the Session\r\n",
    "db.session.new\r\n",
    "\r\n",
    "# persistent objects which currently have changes detected\r\n",
    "# (this collection is now created on the fly each time the property is called)\r\n",
    "db.session.dirty\r\n",
    "\r\n",
    "# persistent objects that have been marked as deleted via session.delete(obj)\r\n",
    "db.session.deleted\r\n",
    "\r\n",
    "# dictionary of all persistent objects, keyed on their identity key\r\n",
    "db.session.identity_map"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "IdentitySet([])"
      ]
     },
     "metadata": {},
     "execution_count": 26
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "IdentitySet([<2:snippet>, <3:Post 3>, <3:analytics>])"
      ]
     },
     "metadata": {},
     "execution_count": 26
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "IdentitySet([])"
      ]
     },
     "metadata": {},
     "execution_count": 26
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<sqlalchemy.orm.identity.WeakInstanceDict at 0x1f58b064520>"
      ]
     },
     "metadata": {},
     "execution_count": 26
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "source": [
    "p3.__dict__"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState at 0x1f58b0d9a30>,\n",
       " 'slug': 'post-3',\n",
       " 'id': 3,\n",
       " 'updated_on': datetime.datetime(2021, 8, 27, 15, 59, 7),\n",
       " 'content': 'Post 3',\n",
       " 'title': 'Post 3',\n",
       " 'category_id': 2,\n",
       " 'created_on': datetime.datetime(2021, 8, 27, 15, 59, 7),\n",
       " 'tags': [<2:snippet>, <3:analytics>, <2:snippet>, <3:analytics>]}"
      ]
     },
     "metadata": {},
     "execution_count": 27
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "source": [
    "from  flask_sqlalchemy import get_debug_queries\r\n",
    "\r\n",
    "get_debug_queries()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "metadata": {},
     "execution_count": 34
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "source": [
    "db.session.commit()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "source": [
    "get_debug_queries()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "metadata": {},
     "execution_count": 36
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "这个提交在 `post_tags` 表中添加了以下五条记录。\r\n",
    "\r\n",
    "```sql\r\n",
    "mysql> select * from post_tags;\r\n",
    "+---------+--------+\r\n",
    "| post_id | tag_id |\r\n",
    "+---------+--------+\r\n",
    "|       1 |      1 |\r\n",
    "|       1 |      2 |\r\n",
    "|       1 |      3 |\r\n",
    "|       2 |      2 |\r\n",
    "|       3 |      2 |\r\n",
    "|       3 |      3 |\r\n",
    "+---------+--------+\r\n",
    "6 rows in set (0.00 sec)\r\n",
    "```\r\n",
    "\r\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "source": [
    "# 我们的帖子现在与一个或多个标签相关联:\r\n",
    "\r\n",
    "p1.tags, p2.tags, p3.tags\r\n",
    "# 可以这样写 \r\n",
    "# for p in [p1,p2,p3]: p.tags"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "([<1:refactoring>, <2:snippet>, <3:analytics>],\n",
       " [<2:snippet>],\n",
       " [<2:snippet>, <3:analytics>])"
      ]
     },
     "metadata": {},
     "execution_count": 30
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<1:refactoring>, <2:snippet>, <3:analytics>]"
      ]
     },
     "metadata": {},
     "execution_count": 30
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<2:snippet>]"
      ]
     },
     "metadata": {},
     "execution_count": 30
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<2:snippet>, <3:analytics>]"
      ]
     },
     "metadata": {},
     "execution_count": 30
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "source": [
    "# 反过来，我们可以访问下面这些属于标签的帖子:\r\n",
    "\r\n",
    "t1.posts, t2.posts, t2.posts\r\n",
    "#可以这样写 \r\n",
    "# for t in [t1,t2,t3]: t.posts"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "([<1:Post 1>],\n",
       " [<1:Post 1>, <2:Post 2>, <3:Post 3>],\n",
       " [<1:Post 1>, <2:Post 2>, <3:Post 3>])"
      ]
     },
     "metadata": {},
     "execution_count": 22
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "source": [
    "# 需要注意的是，与其先提交 Tag 对象，然后再将其与 Post 对象关联，我们可以像下面这样一次完成所有这些操作:\r\n",
    "\r\n",
    "注意，在第11-13行中，我们只是将 `Post` 对象添加到会话中。 `Tag` 和 `Post` 对象通过多对多关系连接。\r\n",
    "因此，将 `Post` 对象添加到会话中也会`隐式地`将其关联的 `Tag` 对象添加到会话中。\r\n",
    "即使您仍然手动向会话中添加 `Tag` 对象 `db.session.add_all([t1, t2, t3]) `，您也不会得到任何错误。\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "source": [
    "# 这里不要重复运行, 不然会重复增加 多套tags.\r\n",
    "t1 = Tag(name=\"refactoring\", slug=\"refactoring\")\r\n",
    "t2 = Tag(name=\"snippet\", slug=\"snippet\")\r\n",
    "t3 = Tag(name=\"analytics\", slug=\"analytics\")\r\n",
    "\r\n",
    "p1.tags.append(t1)\r\n",
    "p1.tags.extend([t2, t3])\r\n",
    "p2.tags.append(t2)\r\n",
    "p3.tags.append(t3)\r\n",
    "\r\n",
    "db.session.add(p1)  # 11-13 行  将 `Post` 对象添加到会话中也会`隐式地`将其关联的 `Tag` 对象添加到会话中。\r\n",
    "db.session.add(p2)  # 11-13 行\r\n",
    "db.session.add(p3)  # 11-13 行\r\n",
    "\r\n",
    "# db.session.add_all([t1, t2, t3])  # 即使您仍然手动向会话中添加 `Tag` 对象 `db.session.add_all([t1, t2, t3]) `，您也不会得到任何错误。\r\n",
    "\r\n",
    "db.session.commit() "
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "source": [
    "# 如果上面重复运行造成多插入了  可以通过这个删除.\r\n",
    "from main2 import post_tags\r\n",
    "from sqlalchemy import delete\r\n",
    "\r\n",
    "# 删除post_tags\r\n",
    "s = delete(post_tags).where(\r\n",
    "    post_tags.c.tag_id > 3\r\n",
    ")\r\n",
    "db.session.execute(s)\r\n",
    "db.session.commit()\r\n",
    "\r\n",
    "# 删除Tag\r\n",
    "db.session.query(Tag).filter(\r\n",
    "    Tag.id  > 3\r\n",
    ").delete(synchronize_session='fetch')\r\n",
    "\r\n",
    "db.session.commit()\r\n",
    "\r\n",
    "# Tag 自增id起始值\r\n",
    "sql = 'alter table tags AUTO_INCREMENT=4;'\r\n",
    "db.session.execute(sql)\r\n",
    "db.session.commit()\r\n",
    "\r\n",
    "t1 = db.session.query(Tag).get(1)\r\n",
    "t2 = db.session.query(Tag).get(2)\r\n",
    "t3 = db.session.query(Tag).get(3)\r\n",
    "\r\n",
    "t1,t2,t3"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x1f58b0cffa0>"
      ]
     },
     "metadata": {},
     "execution_count": 22
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "execution_count": 22
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x1f58b064640>"
      ]
     },
     "metadata": {},
     "execution_count": 22
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 更新数据\r\n",
    "\r\n",
    "要更新对象，只需将其属性设置为新值，将对象添加到会话并提交更改。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "source": [
    "p1.content   # initial value\r\n",
    "\r\n",
    "p1.content = \"This is content for post 1\"   # setting new value\r\n",
    "db.session.add(p1)\r\n",
    "\r\n",
    "db.session.commit()\r\n",
    "\r\n",
    "p1.content  # final value"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "'Post 1'"
      ]
     },
     "metadata": {},
     "execution_count": 33
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "'This is content for post 1'"
      ]
     },
     "metadata": {},
     "execution_count": 33
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 删除数据\r\n",
    "\r\n",
    "要删除对象，请使用会话对象的 delete ()方法。它接受一个对象，并将其标记为在下次提交时删除。\r\n",
    "\r\n",
    "以下试验 先添加一个SEO的TAG, 再后面再将它删除."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "source": [
    "# 创建一个新的临时标签 seo，并将其与后 p1和 p2关联如下:\r\n",
    "\r\n",
    "tmp  = Tag(name='seo', slug='seo')\r\n",
    "\r\n",
    "p1.tags.append(tmp)\r\n",
    "p2.tags.append(tmp)\r\n",
    "\r\n",
    "db.session.add_all([p1, p2])\r\n",
    "db.session.commit()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "这个提交总共添加了3行。一个在 tags 表中，两个在 post_tags 表中。在数据库中，这三行看起来像这样:\r\n",
    "```sql\r\n",
    "mysql> select * from tags;\r\n",
    "+----+-------------+-------------+---------------------+\r\n",
    "| id | name        | slug        | created_on          |\r\n",
    "+----+-------------+-------------+---------------------+\r\n",
    "|  1 | refactoring | refactoring | 2021-08-27 07:47:38 |\r\n",
    "|  2 | snippet     | snippet     | 2021-08-27 07:47:38 |\r\n",
    "|  3 | analytics   | analytics   | 2021-08-27 07:47:38 |\r\n",
    "|  4 | seo         | seo         | 2021-08-27 08:47:51 |  # 这条, 因为前面多加了,又删除\r\n",
    "+----+-------------+-------------+---------------------+\r\n",
    "4 rows in set (0.00 sec)\r\n",
    "\r\n",
    "mysql> select * from post_tags;\r\n",
    "+---------+--------+\r\n",
    "| post_id | tag_id |\r\n",
    "+---------+--------+\r\n",
    "|       1 |      1 |\r\n",
    "|       1 |      2 |\r\n",
    "|       1 |      3 |\r\n",
    "|       2 |      2 |\r\n",
    "|       3 |      2 |\r\n",
    "|       3 |      3 |\r\n",
    "|       1 |      4 | # 这条\r\n",
    "|       2 |      4 | # 这条\r\n",
    "+---------+--------+\r\n",
    "8 rows in set (0.00 sec)\r\n",
    "```"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "让我们现在删除 seo 标签:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "source": [
    "tmp\r\n",
    "\r\n",
    "db.session.delete(tmp)\r\n",
    "db.session.commit()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<4:seo>"
      ]
     },
     "metadata": {},
     "execution_count": 58
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "此提交将删除前一步中添加的所有 `三行` (包括tags 和 post_tags)。但是，它不会删除标记所关联的帖子 `post` 。\r\n",
    "\r\n",
    "默认情况下，如果您删除父表中的对象(如 `categories`) ，那么子表中相关对象的外键(如 posts)设置为 `NULL` 。\r\n",
    "\r\n",
    "下面的清单通过创建一个新的 `categories` 对象和一个 post 对象，然后删除该类别对象来演示这种行为:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "source": [
    "c4 = Category(name='css', slug='css')\r\n",
    "\r\n",
    "p4 = Post(title='Post 4', slug='post-4', content='Post 4', category=c4)\r\n",
    "db.session.add(c4) # 这里只加了C4 但是会附带增加p4\r\n",
    "db.session.new  \r\n",
    "db.session.commit()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "IdentitySet([<None:css>, <None:Post 4>])"
      ]
     },
     "metadata": {},
     "execution_count": 59
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "这个提交添加了两行，一行在 categories 表中，另一行在 posts 表中。\r\n",
    "```sql\r\n",
    "mysql> select * from categories;\r\n",
    "+----+--------+--------+---------------------+\r\n",
    "| id | name   | slug   | created_on          |\r\n",
    "+----+--------+--------+---------------------+\r\n",
    "|  1 | Python | python | 2021-08-27 07:21:51 |\r\n",
    "|  2 | Java   | java   | 2021-08-27 07:21:51 |\r\n",
    "|  3 | css    | css    | 2021-08-27 08:58:14 | # 新增加的\r\n",
    "+----+--------+--------+---------------------+\r\n",
    "3 rows in set (0.00 sec)\r\n",
    "\r\n",
    "mysql> select * from posts;\r\n",
    "+----+--------+--------+----------------------------+---------------------+---------------------+-------------+\r\n",
    "| id | title  | slug   | content                    | created_on          | updated_on          | category_id |\r\n",
    "+----+--------+--------+----------------------------+---------------------+---------------------+-------------+\r\n",
    "|  1 | Post 1 | post-1 | This is content for post 1 | 2021-08-27 07:35:53 | 2021-08-27 08:02:25 |           1 |\r\n",
    "|  2 | Post 2 | post-2 | Post 2                     | 2021-08-27 07:35:53 | 2021-08-27 07:35:53 |           1 |\r\n",
    "|  3 | Post 3 | post-3 | Post 3                     | 2021-08-27 07:35:53 | 2021-08-27 07:35:53 |           2 |\r\n",
    "|  4 | Post 4 | post-4 | Post 4                     | 2021-08-27 08:58:14 | 2021-08-27 08:58:14 |           3 | # 新增加的\r\n",
    "+----+--------+--------+----------------------------+---------------------+---------------------+-------------+\r\n",
    "4 rows in set (0.00 sec)\r\n",
    "```\r\n",
    "\r\n",
    "现在让我们看看删除 Category 对象时会发生什么。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "source": [
    "db.session.delete(c4)\r\n",
    "db.session.commit()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "这样提交会从 categories 表中删除 css 类别，并将相关文章的外键(category_id)设置为 NULL。\r\n",
    "```sql\r\n",
    "mysql> select * from categories;\r\n",
    "+----+--------+--------+---------------------+\r\n",
    "| id | name   | slug   | created_on          |\r\n",
    "+----+--------+--------+---------------------+\r\n",
    "|  1 | Python | python | 2021-08-27 07:21:51 |\r\n",
    "|  2 | Java   | java   | 2021-08-27 07:21:51 |\r\n",
    "+----+--------+--------+---------------------+\r\n",
    "3 rows in set (0.00 sec)\r\n",
    "\r\n",
    "mysql> select * from posts;\r\n",
    "+----+--------+--------+----------------------------+---------------------+---------------------+-------------+\r\n",
    "| id | title  | slug   | content                    | created_on          | updated_on          | category_id |\r\n",
    "+----+--------+--------+----------------------------+---------------------+---------------------+-------------+\r\n",
    "|  1 | Post 1 | post-1 | This is content for post 1 | 2021-08-27 07:35:53 | 2021-08-27 08:02:25 |           1 |\r\n",
    "|  2 | Post 2 | post-2 | Post 2                     | 2021-08-27 07:35:53 | 2021-08-27 07:35:53 |           1 |\r\n",
    "|  3 | Post 3 | post-3 | Post 3                     | 2021-08-27 07:35:53 | 2021-08-27 07:35:53 |           2 |\r\n",
    "|  4 | Post 4 | post-4 | Post 4                     | 2021-08-27 08:58:14 | 2021-08-27 08:58:14 |        null | # null\r\n",
    "+----+--------+--------+----------------------------+---------------------+---------------------+-------------+\r\n",
    "4 rows in set (0.00 sec)\r\n",
    "\r\n",
    "在某些情况下，您可能希望在删除父记录后删除所有子记录。我们可以通过向 db.relationship() 指令传递 cascade='all,delete-orphan' 来实现这一点。打开 main2.py 文件并在 Category 模型中修改 db.relationship ()指令，如下所示(突出显示更改) :\r\n",
    "\r\n",
    "flask_app/main2.py\r\n",
    "#...\r\n",
    "class Category(db.Model):\r\n",
    "    #...\r\n",
    "    posts = db.relationship('Post', backref='category', cascade='all,delete-orphan')\r\n",
    "#...\r\n",
    "```"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "从现在开始，删除一个类别也会删除与之相关的所有帖子。重新启动 shell 以使更改生效，导入必要的对象，并创建一个新类别和一个帖子，如下所示:"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "source": [
    "from main2 import db, Post, Tag, Category\r\n",
    "\r\n",
    "c5 = Category(name='css', slug='css')\r\n",
    "p5 = Post(title='Post 5', slug='post-5', content='Post 5', category=c5)\r\n",
    "\r\n",
    "db.session.add(c5)\r\n",
    "db.session.commit()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "下面是数据库应该如何处理这个提交。\r\n",
    "```sql\r\n",
    "mysql> select * from categories;\r\n",
    "+----+--------+--------+---------------------+\r\n",
    "| id | name   | slug   | created_on          |\r\n",
    "+----+--------+--------+---------------------+\r\n",
    "|  1 | Python | python | 2021-08-27 07:21:51 |\r\n",
    "|  2 | Java   | java   | 2021-08-27 07:21:51 |\r\n",
    "|  4 | css    | css    | 2021-08-27 09:17:54 |\r\n",
    "+----+--------+--------+---------------------+\r\n",
    "3 rows in set (0.00 sec)\r\n",
    "\r\n",
    "mysql> select * from posts;\r\n",
    "+----+--------+--------+----------------------------+---------------------+---------------------+-------------+\r\n",
    "| id | title  | slug   | content                    | created_on          | updated_on          | category_id |\r\n",
    "+----+--------+--------+----------------------------+---------------------+---------------------+-------------+\r\n",
    "|  1 | Post 1 | post-1 | This is content for post 1 | 2021-08-27 07:35:53 | 2021-08-27 08:02:25 |           1 |\r\n",
    "|  2 | Post 2 | post-2 | Post 2                     | 2021-08-27 07:35:53 | 2021-08-27 07:35:53 |           1 |\r\n",
    "|  3 | Post 3 | post-3 | Post 3                     | 2021-08-27 07:35:53 | 2021-08-27 07:35:53 |           2 |\r\n",
    "|  5 | Post 5 | post-5 | Post 5                     | 2021-08-27 09:17:54 | 2021-08-27 09:17:54 |           4 |\r\n",
    "+----+--------+--------+----------------------------+---------------------+---------------------+-------------+\r\n",
    "4 rows in set (0.00 sec)\r\n",
    "```\r\n",
    "\r\n",
    "现在删除类别。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "source": [
    "db.session.delete(c5)\r\n",
    "db.session.commit()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "在这个提交之后，数据库应该是这样的:\r\n",
    "```sql\r\n",
    "mysql> select * from posts;\r\n",
    "+----+--------+--------+----------------------------+---------------------+---------------------+-------------+\r\n",
    "| id | title  | slug   | content                    | created_on          | updated_on          | category_id |\r\n",
    "+----+--------+--------+----------------------------+---------------------+---------------------+-------------+\r\n",
    "|  1 | Post 1 | post-1 | This is content for post 1 | 2021-08-27 07:35:53 | 2021-08-27 08:02:25 |           1 |\r\n",
    "|  2 | Post 2 | post-2 | Post 2                     | 2021-08-27 07:35:53 | 2021-08-27 07:35:53 |           1 |\r\n",
    "|  3 | Post 3 | post-3 | Post 3                     | 2021-08-27 07:35:53 | 2021-08-27 07:35:53 |           2 |\r\n",
    "+----+--------+--------+----------------------------+---------------------+---------------------+-------------+\r\n",
    "3 rows in set (0.00 sec)\r\n",
    "\r\n",
    "mysql> select * from categories;\r\n",
    "+----+--------+--------+---------------------+\r\n",
    "| id | name   | slug   | created_on          |\r\n",
    "+----+--------+--------+---------------------+\r\n",
    "|  1 | Python | python | 2021-08-27 07:21:51 |\r\n",
    "|  2 | Java   | java   | 2021-08-27 07:21:51 |\r\n",
    "+----+--------+--------+---------------------+\r\n",
    "2 rows in set (0.00 sec)\r\n",
    "\r\n",
    "```\r\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 查询数据\r\n",
    "\r\n",
    "要查询数据库，我们使用会话对象的 `query()` 方法。`query()`方法返回一个 `flask_sqlalchemy.BaseQuery` 对象，它只是原始 `sqlalchemy.orm.query` 的扩展查询对象。`flask_sqlalchemy.BaseQuery` 对象表示用于查询数据库的 SELECT 语句。下表列出了一些 `flask_sqlalchemy.BaseQuery` 类的常用的方法。\r\n",
    "\r\n",
    "| Method                        | Description                                                  |\r\n",
    "| :---------------------------- | :----------------------------------------------------------- |\r\n",
    "| `all()`                       | returns the result of the query (represented by `Query`) as a list. 返回查询结果(由 )作为一个列表|\r\n",
    "| `count()`                     | returns the total number of records in the query. 返回查询中的记录总数           |\r\n",
    "| `first()`                     | returns the first result of the query or `None`, if there are no rows in the result. 返回查询的第一个结果,如果没有发现这样的物体则返回`None` |\r\n",
    "| `first_or_404()`              | returns the first result of the query or HTTP 404 Error, if there are no rows in the result. 返回查询的第一个结果,如果没有发现这样的物体则返回 `HTTP 404 Error` |\r\n",
    "| `get(pk)`                     | returns an object that matches the given primary key (pk), or `None`, if no such object is found. 返回与给定主键(pk)匹配的对象，如果没有发现这样的物体则返回`None` |\r\n",
    "| `get_or_404(pk)`                     | returns an object that matches the given primary key (pk), or `None`, if no such object is found. 返回与给定主键(pk)匹配的对象，如果没有发现这样的物体则返回 `HTTP 404 Error` |\r\n",
    "| `filter(*criterion)`          | returns a new `Query` instance after applying the `WHERE` clause to the query. 返回一个新的实例，在应用 `WHERE`子句后|\r\n",
    "| `limit(limit)`                | return a new `Query` instance after applying the `LIMIT` clause to the query. 返回一个新的实例，在应用 `LIMIT`(有限数量)子句后|\r\n",
    "| `offset(offset)`              | return a new `Query` instance after applying the `OFFSET` clause to the query. 返回一个新的实例，在应用 `OFFSET` (偏移)子句后|\r\n",
    "| `order_by(*criterion)`        | return a new `Query` instance after applying `ORDER BY` clause to the query. 返回一个新的实例，在应用 `ORDER BY` 排序子句后|\r\n",
    "| `join(*props, **kwargs)`      | return a new `Query` instance after creating SQL INNER JOIN on the query. 返回一个新的实例，在查询上创建 内连接 之后 |\r\n",
    "| `outerjoin(*props, **kwargs)` | return a new `Query` instance after creating SQL LEFT OUTER JOIN on the query. 返回一个新的实例，在应用 左外连接 后|\r\n",
    "| `group_by(*criterion)`        | return a new `Query` instance after adding `GROUP BY` clause to the query. 返回一个新的实例，在应用 `GROUP BY` (分组统计)子句后|\r\n",
    "| `having(criterion)`           | return a new `Query` instance after adding `HAVING` clause to the query. 返回一个新的实例，在应用 `HAVING` (分组筛选) 子句后|"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### All ()方法\r\n",
    "\r\n",
    "在其最简单的形式中，query ()方法可以接受一个或多个模型类或列作为参数。\r\n",
    "\r\n",
    "下面的代码返回 posts , categories 和 tags 表中的所有记录。\r\n",
    "\r\n",
    "注: 数据的表现形式是由类的 `__repr__` 函数定义"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "source": [
    "db.session.query(Post).all()\r\n",
    "\r\n",
    "db.session.query(Category).all()\r\n",
    "\r\n",
    "db.session.query(Tag).all()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<1:Post 1>, <2:Post 2>, <3:Post 3>]"
      ]
     },
     "metadata": {},
     "execution_count": 65
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<1:Python>, <2:Java>]"
      ]
     },
     "metadata": {},
     "execution_count": 65
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<1:refactoring>, <2:snippet>, <3:analytics>]"
      ]
     },
     "metadata": {},
     "execution_count": 65
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "要获得用于查询数据库的原始 SQL，只需打印 flask_sqlalchemy.BaseQuery 对象如下:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "source": [
    "print(db.session.query(Post))"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT posts.id AS posts_id, posts.title AS posts_title, posts.slug AS posts_slug, posts.content AS posts_content, posts.created_on AS posts_created_on, posts.updated_on AS posts_updated_on, posts.category_id AS posts_category_id \n",
      "FROM posts\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "在前面的示例中，数据从表的所有列返回。我们可以通过以下方式将列名显式传递给 query ()方法来防止这种情况:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "source": [
    "db.session.query(Post.id, Post.title).all()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, 'Post 1'), (2, 'Post 2'), (3, 'Post 3')]"
      ]
     },
     "metadata": {},
     "execution_count": 67
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 以下做了整合 "
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "source": [
    "# count() 方法返回查询返回的结果数。\r\n",
    "\r\n",
    "db.session.query(Post).count() \r\n",
    "db.session.query(Category).count() \r\n",
    "db.session.query(Tag).count() \r\n",
    "\r\n",
    "# first() 方法只返回查询的第一个结果，如果查询返回零结果，则返回 None。\r\n",
    "\r\n",
    "db.session.query(Post).first() \r\n",
    "db.session.query(Category).first() \r\n",
    "db.session.query(Tag).first() \r\n",
    "\r\n",
    "# Get() 方法返回与传递给它的主键相匹配的实例，如果没有找到这样的对象，则返回 None。\r\n",
    "\r\n",
    "db.session.query(Post).get(1) \r\n",
    "db.session.query(Category).get(2) \r\n",
    "db.session.query(Tag).get(3) \r\n",
    "\r\n",
    "# Get404() 方法返回与传递给它的主键相匹配的实例，如果没有找到这样的对象，则返回 HTTP 404 错误。\r\n",
    "\r\n",
    "db.session.query(Post).get_or_404(1)\r\n",
    "db.session.query(Post).get_or_404(100)\r\n"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "metadata": {},
     "execution_count": 69
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "metadata": {},
     "execution_count": 69
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "metadata": {},
     "execution_count": 69
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<1:Post 1>"
      ]
     },
     "metadata": {},
     "execution_count": 69
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<1:Python>"
      ]
     },
     "metadata": {},
     "execution_count": 69
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<1:refactoring>"
      ]
     },
     "metadata": {},
     "execution_count": 69
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<1:Post 1>"
      ]
     },
     "metadata": {},
     "execution_count": 69
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<2:Java>"
      ]
     },
     "metadata": {},
     "execution_count": 69
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<3:analytics>"
      ]
     },
     "metadata": {},
     "execution_count": 69
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<1:Post 1>"
      ]
     },
     "metadata": {},
     "execution_count": 69
    },
    {
     "output_type": "error",
     "ename": "NotFound",
     "evalue": "404 Not Found: The requested URL was not found on the server. If you entered the URL manually please check your spelling and try again.",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNotFound\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp/ipykernel_116448/158962157.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     20\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     21\u001b[0m \u001b[0mdb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msession\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mPost\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_or_404\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 22\u001b[1;33m \u001b[0mdb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msession\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mPost\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_or_404\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m100\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     23\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     24\u001b[0m \u001b[1;31m# Filter() 方法允许我们通过向查询中添加 WHERE 子句来过滤结果。它至少接受一个列、一个运算符和一个值。下面是一个例子:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mE:\\PYTHON39\\lib\\site-packages\\flask_sqlalchemy\\__init__.py\u001b[0m in \u001b[0;36mget_or_404\u001b[1;34m(self, ident, description)\u001b[0m\n\u001b[0;32m    460\u001b[0m         \u001b[0mrv\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mident\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    461\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mrv\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 462\u001b[1;33m             \u001b[0mabort\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m404\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdescription\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdescription\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    463\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mrv\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    464\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mE:\\PYTHON39\\lib\\site-packages\\werkzeug\\exceptions.py\u001b[0m in \u001b[0;36mabort\u001b[1;34m(status, *args, **kwargs)\u001b[0m\n\u001b[0;32m    820\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    821\u001b[0m     \"\"\"\n\u001b[1;32m--> 822\u001b[1;33m     \u001b[1;32mreturn\u001b[0m \u001b[0m_aborter\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstatus\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    823\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    824\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mE:\\PYTHON39\\lib\\site-packages\\werkzeug\\exceptions.py\u001b[0m in \u001b[0;36m__call__\u001b[1;34m(self, code, *args, **kwargs)\u001b[0m\n\u001b[0;32m    805\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mcode\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmapping\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    806\u001b[0m             \u001b[1;32mraise\u001b[0m \u001b[0mLookupError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"no exception for %r\"\u001b[0m \u001b[1;33m%\u001b[0m \u001b[0mcode\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 807\u001b[1;33m         \u001b[1;32mraise\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmapping\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcode\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    808\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    809\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNotFound\u001b[0m: 404 Not Found: The requested URL was not found on the server. If you entered the URL manually please check your spelling and try again."
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "source": [
    "# Filter() 方法允许我们通过向查询中添加 WHERE 子句来过滤结果。它至少接受一个列、一个运算符和一个值。下面是一个例子:\r\n",
    "\r\n",
    "# 这个查询返回所有标题为“ Post 1”的帖子:\r\n",
    "db.session.query(Post).filter(Post.title == 'Post 1').all()\r\n",
    "print(db.session.query(Post).filter(Post.title == 'Post 1'))"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<1:Post 1>]"
      ]
     },
     "metadata": {},
     "execution_count": 70
    },
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT posts.id AS posts_id, posts.title AS posts_title, posts.slug AS posts_slug, posts.content AS posts_content, posts.created_on AS posts_created_on, posts.updated_on AS posts_updated_on, posts.category_id AS posts_category_id \n",
      "FROM posts \n",
      "WHERE posts.title = %(title_1)s\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "source": [
    "# WHERE 子句中的字符串 `%(title_1)s` 是一个占位符，在执行查询时将被实际值替换。\r\n",
    "# 我们可以向 filter()方法传递多个过滤器，并且它们将使用 SQL 语言的 `and` 操作符连接在一起。例如:\r\n",
    "\r\n",
    "db.session.query(Post).filter(Post.id >= 1, Post.id <= 2).all()\r\n",
    "# 此查询返回主键大于或等于1但小于或等于2的所有职位。它的 SQL 对等语句是:\r\n",
    "\r\n",
    "print(db.session.query(Post).filter(Post.id >= 1, Post.id <= 2))"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<1:Post 1>, <2:Post 2>]"
      ]
     },
     "metadata": {},
     "execution_count": 71
    },
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT posts.id AS posts_id, posts.title AS posts_title, posts.slug AS posts_slug, posts.content AS posts_content, posts.created_on AS posts_created_on, posts.updated_on AS posts_updated_on, posts.category_id AS posts_category_id \n",
      "FROM posts \n",
      "WHERE posts.id >= %(id_1)s AND posts.id <= %(id_2)s\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "source": [
    "# first_or_404() 与 first ()方法相同，但当查询不返回结果时，它不返回 None，而是返回 HTTP 404 Error。\r\n",
    "\r\n",
    "db.session.query(Post).filter(Post.id > 1).first_or_404()\r\n",
    "\r\n",
    "db.session.query(Post).filter(Post.id > 10).first_or_404().all()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<2:Post 2>"
      ]
     },
     "metadata": {},
     "execution_count": 72
    },
    {
     "output_type": "error",
     "ename": "NotFound",
     "evalue": "404 Not Found: The requested URL was not found on the server. If you entered the URL manually please check your spelling and try again.",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNotFound\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp/ipykernel_116448/2634973434.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[0mdb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msession\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mPost\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfilter\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mPost\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mid\u001b[0m \u001b[1;33m>\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfirst_or_404\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m \u001b[0mdb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msession\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mPost\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfilter\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mPost\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mid\u001b[0m \u001b[1;33m>\u001b[0m \u001b[1;36m10\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfirst_or_404\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mall\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32mE:\\PYTHON39\\lib\\site-packages\\flask_sqlalchemy\\__init__.py\u001b[0m in \u001b[0;36mfirst_or_404\u001b[1;34m(self, description)\u001b[0m\n\u001b[0;32m    468\u001b[0m         \u001b[0mrv\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfirst\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    469\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mrv\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 470\u001b[1;33m             \u001b[0mabort\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m404\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdescription\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdescription\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    471\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mrv\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    472\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mE:\\PYTHON39\\lib\\site-packages\\werkzeug\\exceptions.py\u001b[0m in \u001b[0;36mabort\u001b[1;34m(status, *args, **kwargs)\u001b[0m\n\u001b[0;32m    820\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    821\u001b[0m     \"\"\"\n\u001b[1;32m--> 822\u001b[1;33m     \u001b[1;32mreturn\u001b[0m \u001b[0m_aborter\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstatus\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    823\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    824\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mE:\\PYTHON39\\lib\\site-packages\\werkzeug\\exceptions.py\u001b[0m in \u001b[0;36m__call__\u001b[1;34m(self, code, *args, **kwargs)\u001b[0m\n\u001b[0;32m    805\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mcode\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmapping\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    806\u001b[0m             \u001b[1;32mraise\u001b[0m \u001b[0mLookupError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"no exception for %r\"\u001b[0m \u001b[1;33m%\u001b[0m \u001b[0mcode\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 807\u001b[1;33m         \u001b[1;32mraise\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmapping\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcode\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    808\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    809\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNotFound\u001b[0m: 404 Not Found: The requested URL was not found on the server. If you entered the URL manually please check your spelling and try again."
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "source": [
    "# LIMIT ()方法向查询添加 LIMIT 子句。它接受您希望从查询返回的行数。\r\n",
    "\r\n",
    "db.session.query(Post).limit(2).all() # 必须加all(), 不然返回的是一个 `flask_sqlalchemy.BaseQuery` 对象 \r\n",
    "\r\n",
    "db.session.query(Post).limit(2) # 返回的是一个 `flask_sqlalchemy.BaseQuery` 对象 \r\n",
    "\r\n",
    "db.session.query(Post).filter(Post.id >= 2).limit(1).all() \r\n",
    "\r\n",
    "print(db.session.query(Post).limit(2))\r\n",
    "\r\n",
    "print(db.session.query(Post).filter(Post.id >= 2).limit(1))\r\n",
    "\r\n",
    "# OFFSET ()方法将 OFFSET 子句添加到查询中。它接受偏移量作为参数。它通常与 limit ()子句一起使用。\r\n",
    "\r\n",
    "db.session.query(Post).filter(Post.id > 1).limit(3).offset(1).all()\r\n",
    "\r\n",
    "print(db.session.query(Post).filter(Post.id > 1).limit(3).offset(1))\r\n",
    "\r\n",
    "# ORDER_BY ()方法通过向查询中添加 orderby 子句来对结果进行排序。它接受订单所基于的列名。默认情况下，它按升序排序。\r\n",
    "\r\n",
    "db.session.query(Tag).all()\r\n",
    "\r\n",
    "db.session.query(Tag).order_by(Tag.name).all()\r\n",
    "\r\n",
    "# desc asc 使用 db.desc ()函数按降序排序如下:\r\n",
    "db.session.query(Tag).order_by(db.desc(Tag.name)).all()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<1:Post 1>, <2:Post 2>]"
      ]
     },
     "metadata": {},
     "execution_count": 77
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<flask_sqlalchemy.BaseQuery at 0x2892bb3c7f0>"
      ]
     },
     "metadata": {},
     "execution_count": 77
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<2:Post 2>]"
      ]
     },
     "metadata": {},
     "execution_count": 77
    },
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT posts.id AS posts_id, posts.title AS posts_title, posts.slug AS posts_slug, posts.content AS posts_content, posts.created_on AS posts_created_on, posts.updated_on AS posts_updated_on, posts.category_id AS posts_category_id \n",
      "FROM posts \n",
      " LIMIT %(param_1)s\n",
      "SELECT posts.id AS posts_id, posts.title AS posts_title, posts.slug AS posts_slug, posts.content AS posts_content, posts.created_on AS posts_created_on, posts.updated_on AS posts_updated_on, posts.category_id AS posts_category_id \n",
      "FROM posts \n",
      "WHERE posts.id >= %(id_1)s \n",
      " LIMIT %(param_1)s\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "source": [
    "# join 方法用于创建 sql join。它接受要为其创建 sql join 的表名。\r\n",
    "\r\n",
    "db.session.query(Post).join(Category).all()\r\n",
    "\r\n",
    "print(db.session.query(Post).join(Category))\r\n",
    "\r\n",
    "# Join ()方法通常用于在单个查询中从一个或多个表获取数据。例如:\r\n",
    "db.session.query(Post.title, Category.name).join(Category).all()\r\n",
    "\r\n",
    "# 通过链接 JOIN ()方法，我们可以为两个以上的表创建 SQL JOIN:\r\n",
    "# db.session.query(Table1).join(Table2).join(Table3).join(Table4).all()\r\n",
    "\r\n"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[<1:Post 1>, <2:Post 2>, <3:Post 3>]"
      ]
     },
     "metadata": {},
     "execution_count": 80
    },
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT posts.id AS posts_id, posts.title AS posts_title, posts.slug AS posts_slug, posts.content AS posts_content, posts.created_on AS posts_created_on, posts.updated_on AS posts_updated_on, posts.category_id AS posts_category_id \n",
      "FROM posts INNER JOIN categories ON categories.id = posts.category_id\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Post 1', 'Python'), ('Post 2', 'Python'), ('Post 3', 'Java')]"
      ]
     },
     "metadata": {},
     "execution_count": 80
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 让我们通过完成我们的联系表格来结束本课。\r\n",
    "\r\n",
    "回想一下，在 Flask 表单处理课程中，我们创建了一个联系表单来接收用户的反馈。就目前情况而言，`contact()` 视图函数不会将提交的反馈保存到数据库中。它只将反馈打印到控制台。为了将反馈保存到数据库，我们必须首先创建一个新表。打开 main2.py，在 Tag 模型下面添加 Feedback 模型，如下所示:\r\n",
    "\r\n",
    "flask_app/main2.py\r\n",
    "\r\n",
    "```python\r\n",
    "#...\r\n",
    "class Feedback(db.Model):\r\n",
    "    __tablename__ = 'feedbacks'\r\n",
    "    id = db.Column(db.Integer(), primary_key=True)\r\n",
    "    name = db.Column(db.String(1000), nullable=False)\r\n",
    "    email = db.Column(db.String(100), nullable=False)\r\n",
    "    message = db.Column(db.Text(), nullable=False)\r\n",
    "    created_on = db.Column(db.DateTime(), default=datetime.utcnow)\r\n",
    "\r\n",
    "    def __repr__(self):\r\n",
    "        return \"<{}:{}>\".format(self.id, self.name)\r\n",
    "#...\r\n",
    "```\r\n",
    "\r\n",
    "重新启动 Python shell 并调用 db 对象的 create_all() 方法来创建反馈表。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "source": [
    "from main2 import db\r\n",
    "\r\n",
    "db.create_all()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "接下来，修改 contact ()视图函数如下(突出显示更改) :\r\n",
    "\r\n",
    "flask_app/main2.py\r\n",
    "\r\n",
    "```python\r\n",
    "#...\r\n",
    "@app.route('/contact/', methods=['get', 'post'])\r\n",
    "def contact():\r\n",
    "    form = ContactForm()\r\n",
    "    if form.validate_on_submit():\r\n",
    "        name = form.name.data\r\n",
    "        email = form.email.data\r\n",
    "        message = form.message.data\r\n",
    "        print(name)\r\n",
    "        print(Post)\r\n",
    "        print(email)\r\n",
    "        print(message)\r\n",
    "\r\n",
    "        # db logic goes here\r\n",
    "        feedback = Feedback(name=name, email=email, message=message)\r\n",
    "        db.session.add(feedback)\r\n",
    "        db.session.commit()\r\n",
    "\r\n",
    "        print(\"\\nData received. Now redirecting ...\")\r\n",
    "        flash(\"Message Received\", \"success\")\r\n",
    "        return redirect(url_for('contact'))\r\n",
    "\r\n",
    "    return render_template('contact.html', form=form)\r\n",
    "#...\r\n",
    "```\r\n",
    "\r\n",
    "启动服务器，访问 http://127.0.0.1:5000/contact/ ，填写表格并提交反馈。\r\n",
    "\r\n",
    "` python main2.py runserver `\r\n",
    "\r\n",
    "在 MYSQL 中提交的反馈应该是这样的:\r\n",
    "\r\n",
    "```sql\r\n",
    "mysql> select * from Feedbacks\r\n",
    "    -> ;\r\n",
    "+----+---------------+----------------+---------+---------------------+\r\n",
    "| id | name          | email          | message | created_on          |\r\n",
    "+----+---------------+----------------+---------+---------------------+\r\n",
    "|  1 | Fluent Python | 4876270@qq.com | success | 2021-08-27 10:02:14 |\r\n",
    "+----+---------------+----------------+---------+---------------------+\r\n",
    "1 row in set (0.00 sec)\r\n",
    "```"
   ],
   "metadata": {}
  }
 ],
 "metadata": {
  "orig_nbformat": 4,
  "language_info": {
   "name": "python",
   "version": "3.9.6",
   "mimetype": "text/x-python",
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "pygments_lexer": "ipython3",
   "nbconvert_exporter": "python",
   "file_extension": ".py"
  },
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.9.6 64-bit (system)"
  },
  "interpreter": {
   "hash": "c644d696b95f5e0f4df3c6556741cf30bcf9ea6ca93c3e1f29fcf31d885534fc"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}